package top.chengdongqing.mishop.repositories

import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import top.chengdongqing.mishop.entities.ProductReview

interface ProductReviewRepository : JpaRepository<ProductReview, Int> {

    @Query(
        """
        FROM ProductReview r
        WHERE r.productId = :productId
            AND (:rating IS NULL OR r.rating = :rating)
            AND (:withPhotosOnly IS NULL OR :withPhotosOnly = FALSE OR r.photoUrls IS NOT NULL)
        ORDER BY
            r.createdAt DESC
    """
    )
    fun findAllByPage(productId: Int, rating: Int?, withPhotosOnly: Boolean?, pageable: Pageable): Page<ProductReview>

    fun findByOrderIdAndProductId(orderId: Int, productId: Int): ProductReview?

    fun existsByOrderIdAndProductId(orderId: Int, productId: Int): Boolean

    fun countByProductId(productId: Int): Int

    fun countByProductIdAndRatingGreaterThanEqual(productId: Int, rating: Int): Int

    @Query(
        """
        SELECT
            r.rating AS key,
            count(*) AS value
        FROM
            ProductReview r
        WHERE
            r.productId = ?1
        GROUP BY
            r.rating
    """
    )
    fun countByProductIdGroupByScore(productId: Int): List<Array<Int>>
}